# ==============================
# _*_ coding:utf-8 _*_
# _*_ by:zizle     _*_
# _*_ at:20230423  _*_
# ==============================
import json
import time
import pandas as pd
from urllib3 import disable_warnings
import requests

disable_warnings()

from db_utils import BaseDatabaseConnection
def add_future_spot_basic():
    data = {"code":200,"msg":"操作成功","data":[{"variety_en":"SA","contract":"SA2309","close_price":2226.0,"lcontract":"2309","price":2900.0,"basic_date":"2023-04-21","basic_value":674.0,"close_price_diff":6.0,"price_diff":0.0,"basic_diff":-6.0,"basic_rate":0.3028,"variety_name":"纯碱"},{"variety_en":"I","contract":"I2309","close_price":730.5,"lcontract":"2309","price":906.0,"basic_date":"2023-04-21","basic_value":175.5,"close_price_diff":-31.0,"price_diff":-21.0,"basic_diff":10.0,"basic_rate":0.2402,"variety_name":"铁矿"},{"variety_en":"M","contract":"M2309","close_price":3405.0,"lcontract":"2309","price":4170.0,"basic_date":"2023-04-21","basic_value":765.0,"close_price_diff":-40.0,"price_diff":-60.0,"basic_diff":-20.0,"basic_rate":0.2247,"variety_name":"豆粕"},{"variety_en":"UR","contract":"UR2309","close_price":2034.0,"lcontract":"2309","price":2490.0,"basic_date":"2023-04-21","basic_value":456.0,"close_price_diff":-54.0,"price_diff":0.0,"basic_diff":54.0,"basic_rate":0.2242,"variety_name":"尿素"},{"variety_en":"JM","contract":"JM2309","close_price":1486.5,"lcontract":"2309","price":1800.0,"basic_date":"2023-04-21","basic_value":313.5,"close_price_diff":-39.0,"price_diff":0.0,"basic_diff":39.0,"basic_rate":0.2109,"variety_name":"焦煤"},{"variety_en":"J","contract":"J2309","close_price":2271.0,"lcontract":"2309","price":2670.0,"basic_date":"2023-04-21","basic_value":399.0,"close_price_diff":-41.0,"price_diff":0.0,"basic_diff":41.0,"basic_rate":0.1757,"variety_name":"焦炭"},{"variety_en":"JD","contract":"JD2309","close_price":4269.0,"lcontract":"2309","price":4895.0,"basic_date":"2023-04-21","basic_value":626.0,"close_price_diff":-10.0,"price_diff":0.0,"basic_diff":10.0,"basic_rate":0.1466,"variety_name":"鸡蛋"},{"variety_en":"TA","contract":"TA2309","close_price":5726.0,"lcontract":"2309","price":6350.0,"basic_date":"2023-04-21","basic_value":624.0,"close_price_diff":-54.0,"price_diff":0.0,"basic_diff":54.0,"basic_rate":0.109,"variety_name":"PTA"},{"variety_en":"Y","contract":"Y2309","close_price":7550.0,"lcontract":"2309","price":8340.0,"basic_date":"2023-04-21","basic_value":790.0,"close_price_diff":-194.0,"price_diff":-180.0,"basic_diff":14.0,"basic_rate":0.1046,"variety_name":"豆油"},{"variety_en":"RM","contract":"RM2309","close_price":2790.0,"lcontract":"2309","price":3080.0,"basic_date":"2023-04-21","basic_value":290.0,"close_price_diff":-42.0,"price_diff":-20.0,"basic_diff":22.0,"basic_rate":0.1039,"variety_name":"菜粕"},{"variety_en":"PK","contract":"PK2310","close_price":10236.0,"lcontract":"2310","price":11200.0,"basic_date":"2023-04-21","basic_value":964.0,"close_price_diff":-226.0,"price_diff":0.0,"basic_diff":226.0,"basic_rate":0.0942,"variety_name":"花生"},{"variety_en":"P","contract":"P2309","close_price":7080.0,"lcontract":"2309","price":7730.0,"basic_date":"2023-04-21","basic_value":650.0,"close_price_diff":-152.0,"price_diff":-50.0,"basic_diff":102.0,"basic_rate":0.0918,"variety_name":"棕榈油"},{"variety_en":"BU","contract":"BU2307","close_price":3733.0,"lcontract":"2307","price":4020.0,"basic_date":"2023-04-21","basic_value":287.0,"close_price_diff":-141.0,"price_diff":0.0,"basic_diff":141.0,"basic_rate":0.0769,"variety_name":"沥青"},{"variety_en":"A","contract":"A2307","close_price":4870.0,"lcontract":"2307","price":5200.0,"basic_date":"2023-04-21","basic_value":330.0,"close_price_diff":-87.0,"price_diff":-50.0,"basic_diff":37.0,"basic_rate":0.0678,"variety_name":"豆一"},{"variety_en":"HC","contract":"HC2310","close_price":3883.0,"lcontract":"2310","price":4140.0,"basic_date":"2023-04-21","basic_value":257.0,"close_price_diff":-90.0,"price_diff":-40.0,"basic_diff":50.0,"basic_rate":0.0662,"variety_name":"热卷"},{"variety_en":"CY","contract":"CY2305","close_price":21740.0,"lcontract":"2305","price":23110.0,"basic_date":"2023-04-21","basic_value":1370.0,"close_price_diff":-435.0,"price_diff":0.0,"basic_diff":435.0,"basic_rate":0.063,"variety_name":"棉纱"},{"variety_en":"NI","contract":"NI2306","close_price":185200.0,"lcontract":"2306","price":196250.0,"basic_date":"2023-04-21","basic_value":11050.0,"close_price_diff":-5210.0,"price_diff":-4950.0,"basic_diff":260.0,"basic_rate":0.0597,"variety_name":"沪镍"},{"variety_en":"CS","contract":"CS2307","close_price":2974.0,"lcontract":"2307","price":3150.0,"basic_date":"2023-04-21","basic_value":176.0,"close_price_diff":-21.0,"price_diff":0.0,"basic_diff":21.0,"basic_rate":0.0592,"variety_name":"淀粉"},{"variety_en":"CF","contract":"CF2309","close_price":14855.0,"lcontract":"2309","price":15735.0,"basic_date":"2023-04-21","basic_value":880.0,"close_price_diff":-320.0,"price_diff":-7.0,"basic_diff":313.0,"basic_rate":0.0592,"variety_name":"棉花"},{"variety_en":"SS","contract":"SS2306","close_price":15280.0,"lcontract":"2306","price":16050.0,"basic_date":"2023-04-21","basic_value":770.0,"close_price_diff":-100.0,"price_diff":0.0,"basic_diff":100.0,"basic_rate":0.0504,"variety_name":"不锈钢"},{"variety_en":"OI","contract":"OI2309","close_price":8357.0,"lcontract":"2309","price":8720.0,"basic_date":"2023-04-21","basic_value":363.0,"close_price_diff":-160.0,"price_diff":-60.0,"basic_diff":100.0,"basic_rate":0.0434,"variety_name":"菜油"},{"variety_en":"RB","contract":"RB2310","close_price":3810.0,"lcontract":"2310","price":3970.0,"basic_date":"2023-04-21","basic_value":160.0,"close_price_diff":-104.0,"price_diff":-50.0,"basic_diff":54.0,"basic_rate":0.042,"variety_name":"螺纹"},{"variety_en":"SP","contract":"SP2309","close_price":5208.0,"lcontract":"2309","price":5400.0,"basic_date":"2023-04-21","basic_value":192.0,"close_price_diff":-134.0,"price_diff":-100.0,"basic_diff":34.0,"basic_rate":0.0369,"variety_name":"纸浆"},{"variety_en":"MA","contract":"MA2309","close_price":2472.0,"lcontract":"2309","price":2560.0,"basic_date":"2023-04-21","basic_value":88.0,"close_price_diff":7.0,"price_diff":20.0,"basic_diff":13.0,"basic_rate":0.0356,"variety_name":"甲醇"},{"variety_en":"C","contract":"C2307","close_price":2672.0,"lcontract":"2307","price":2730.0,"basic_date":"2023-04-21","basic_value":58.0,"close_price_diff":-38.0,"price_diff":0.0,"basic_diff":38.0,"basic_rate":0.0217,"variety_name":"玉米"},{"variety_en":"SM","contract":"SM2306","close_price":6920.0,"lcontract":"2306","price":7050.0,"basic_date":"2023-04-21","basic_value":130.0,"close_price_diff":-214.0,"price_diff":-100.0,"basic_diff":114.0,"basic_rate":0.0188,"variety_name":"锰硅"},{"variety_en":"SF","contract":"SF2306","close_price":7444.0,"lcontract":"2306","price":7550.0,"basic_date":"2023-04-21","basic_value":106.0,"close_price_diff":-160.0,"price_diff":-50.0,"basic_diff":110.0,"basic_rate":0.0142,"variety_name":"硅铁"},{"variety_en":"SI","contract":"SI2308","close_price":15140.0,"lcontract":"2308","price":15350.0,"basic_date":"2023-04-21","basic_value":210.0,"close_price_diff":-20.0,"price_diff":-100.0,"basic_diff":-80.0,"basic_rate":0.0139,"variety_name":"工业硅"},{"variety_en":"ZN","contract":"ZN2306","close_price":21880.0,"lcontract":"2306","price":22180.0,"basic_date":"2023-04-21","basic_value":300.0,"close_price_diff":-130.0,"price_diff":20.0,"basic_diff":150.0,"basic_rate":0.0137,"variety_name":"沪锌"},{"variety_en":"L","contract":"L2309","close_price":8223.0,"lcontract":"2309","price":8300.0,"basic_date":"2023-04-21","basic_value":77.0,"close_price_diff":28.0,"price_diff":0.0,"basic_diff":-28.0,"basic_rate":0.0094,"variety_name":"塑料"},{"variety_en":"PP","contract":"PP2309","close_price":7573.0,"lcontract":"2309","price":7630.0,"basic_date":"2023-04-21","basic_value":57.0,"close_price_diff":25.0,"price_diff":0.0,"basic_diff":-25.0,"basic_rate":0.0075,"variety_name":"聚丙烯"},{"variety_en":"AL","contract":"AL2306","close_price":18890.0,"lcontract":"2306","price":19030.0,"basic_date":"2023-04-21","basic_value":140.0,"close_price_diff":-315.0,"price_diff":-80.0,"basic_diff":235.0,"basic_rate":0.0074,"variety_name":"沪铝"},{"variety_en":"EB","contract":"EB2306","close_price":8462.0,"lcontract":"2306","price":8515.0,"basic_date":"2023-04-21","basic_value":53.0,"close_price_diff":-66.0,"price_diff":-155.0,"basic_diff":-89.0,"basic_rate":0.0063,"variety_name":"苯乙烯"},{"variety_en":"SR","contract":"SR2307","close_price":6903.0,"lcontract":"2307","price":6940.0,"basic_date":"2023-04-21","basic_value":37.0,"close_price_diff":122.0,"price_diff":140.0,"basic_diff":18.0,"basic_rate":0.0054,"variety_name":"白糖"},{"variety_en":"AU","contract":"AU2306","close_price":442.5,"lcontract":"2306","price":444.69,"basic_date":"2023-04-21","basic_value":2.19,"close_price_diff":-2.04,"price_diff":1.44,"basic_diff":3.48,"basic_rate":0.0049,"variety_name":"黄金"},{"variety_en":"AG","contract":"AG2306","close_price":5609.0,"lcontract":"2306","price":5629.0,"basic_date":"2023-04-21","basic_value":20.0,"close_price_diff":-55.0,"price_diff":-7.0,"basic_diff":48.0,"basic_rate":0.0036,"variety_name":"白银"},{"variety_en":"CU","contract":"CU2305","close_price":68660.0,"lcontract":"2305","price":68890.0,"basic_date":"2023-04-21","basic_value":230.0,"close_price_diff":-690.0,"price_diff":-615.0,"basic_diff":75.0,"basic_rate":0.0033,"variety_name":"沪铜"},{"variety_en":"SN","contract":"SN2306","close_price":212550.0,"lcontract":"2306","price":213250.0,"basic_date":"2023-04-21","basic_value":700.0,"close_price_diff":-6970.0,"price_diff":-5750.0,"basic_diff":1220.0,"basic_rate":0.0033,"variety_name":"沪锡"},{"variety_en":"FG","contract":"FG2309","close_price":1807.0,"lcontract":"2309","price":1804.0,"basic_date":"2023-04-21","basic_value":-3.0,"close_price_diff":-43.0,"price_diff":20.0,"basic_diff":63.0,"basic_rate":-0.0017,"variety_name":"玻璃"},{"variety_en":"PB","contract":"PB2306","close_price":15290.0,"lcontract":"2306","price":15225.0,"basic_date":"2023-04-21","basic_value":-65.0,"close_price_diff":-120.0,"price_diff":-50.0,"basic_diff":70.0,"basic_rate":-0.0043,"variety_name":"沪铅"},{"variety_en":"V","contract":"V2309","close_price":6198.0,"lcontract":"2309","price":6160.0,"basic_date":"2023-04-21","basic_value":-38.0,"close_price_diff":-2.0,"price_diff":10.0,"basic_diff":12.0,"basic_rate":-0.0061,"variety_name":"PVC"},{"variety_en":"PF","contract":"PF2306","close_price":7428.0,"lcontract":"2306","price":7370.0,"basic_date":"2023-04-21","basic_value":-58.0,"close_price_diff":-54.0,"price_diff":-10.0,"basic_diff":44.0,"basic_rate":-0.0078,"variety_name":"短纤"},{"variety_en":"RU","contract":"RU2309","close_price":11860.0,"lcontract":"2309","price":11700.0,"basic_date":"2023-04-21","basic_value":-160.0,"close_price_diff":-180.0,"price_diff":100.0,"basic_diff":280.0,"basic_rate":-0.0135,"variety_name":"天然橡胶"},{"variety_en":"EG","contract":"EG2309","close_price":4350.0,"lcontract":"2309","price":4187.0,"basic_date":"2023-04-21","basic_value":-163.0,"close_price_diff":32.0,"price_diff":49.0,"basic_diff":17.0,"basic_rate":-0.0375,"variety_name":"乙二醇"},{"variety_en":"LH","contract":"LH2307","close_price":16810.0,"lcontract":"2307","price":14500.0,"basic_date":"2023-04-21","basic_value":-2310.0,"close_price_diff":250.0,"price_diff":-100.0,"basic_diff":-350.0,"basic_rate":-0.1374,"variety_name":"生猪"},{"variety_en":"CJ","contract":"CJ2309","close_price":10060.0,"lcontract":"2309","price":8400.0,"basic_date":"2023-04-21","basic_value":-1660.0,"close_price_diff":-320.0,"price_diff":0.0,"basic_diff":320.0,"basic_rate":-0.165,"variety_name":"红枣"}]}

    table = data['data']
    in_data = []
    for r in table:
        in_data.append({
            'date': r['basic_date'],
            'code': r['variety_en'],
            'name': r['variety_name'],
            'close': r['close_price'],
            'close_change': r['close_price_diff'],
            'spot': r['price'],
            'spot_change': r['price_diff']
        })

    sql = """
        INSERT INTO ruizhi.quote_future_spot 
        (date, code, name, close, close_change, spot, spot_change) VALUES 
        (%(date)s, %(code)s, %(name)s, %(close)s, %(close_change)s, %(spot)s, %(spot_change)s);
    """
    with BaseDatabaseConnection() as conn:
        c = conn.cursor.executemany(sql, in_data)
        print('插入完成', c)

def add_future_position():
    data = {"code":200,"msg":"操作成功","data":{"rank_data":[{"variety_en":"A","long_position":174803,"short_position":175109,"variety_name":"豆一","quote_date":"2023-04-21","net_position":-306,"long_diff":9835,"short_diff":7941,"net_diff":1894},{"variety_en":"AG","long_position":597492,"short_position":500886,"variety_name":"白银","quote_date":"2023-04-21","net_position":96606,"long_diff":-9557,"short_diff":-2027,"net_diff":-7530},{"variety_en":"AL","long_position":337255,"short_position":319708,"variety_name":"沪铝","quote_date":"2023-04-21","net_position":17547,"long_diff":-17837,"short_diff":-19448,"net_diff":1611},{"variety_en":"AP","long_position":119834,"short_position":119464,"variety_name":"苹果","quote_date":"2023-04-21","net_position":370,"long_diff":9877,"short_diff":9745,"net_diff":132},{"variety_en":"AU","long_position":168295,"short_position":131276,"variety_name":"黄金","quote_date":"2023-04-21","net_position":37019,"long_diff":-887,"short_diff":295,"net_diff":-1182},{"variety_en":"B","long_position":24876,"short_position":29012,"variety_name":"豆二","quote_date":"2023-04-21","net_position":-4136,"long_diff":3396,"short_diff":3176,"net_diff":220},{"variety_en":"BB","long_position":0,"short_position":0,"variety_name":"胶板","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"BC","long_position":32560,"short_position":31993,"variety_name":"国际铜","quote_date":"2023-04-21","net_position":567,"long_diff":-91,"short_diff":-243,"net_diff":152},{"variety_en":"BU","long_position":439390,"short_position":430541,"variety_name":"沥青","quote_date":"2023-04-21","net_position":8849,"long_diff":-15134,"short_diff":-19547,"net_diff":4413},{"variety_en":"C","long_position":1286109,"short_position":1237282,"variety_name":"玉米","quote_date":"2023-04-21","net_position":48827,"long_diff":43992,"short_diff":24619,"net_diff":19373},{"variety_en":"CF","long_position":600844,"short_position":706815,"variety_name":"棉花","quote_date":"2023-04-21","net_position":-105971,"long_diff":-20575,"short_diff":-24231,"net_diff":3656},{"variety_en":"CJ","long_position":28079,"short_position":38808,"variety_name":"红枣","quote_date":"2023-04-21","net_position":-10729,"long_diff":-3179,"short_diff":-4735,"net_diff":1556},{"variety_en":"CS","long_position":259056,"short_position":253275,"variety_name":"淀粉","quote_date":"2023-04-21","net_position":5781,"long_diff":-6178,"short_diff":-11321,"net_diff":5143},{"variety_en":"CU","long_position":267654,"short_position":251864,"variety_name":"沪铜","quote_date":"2023-04-21","net_position":15790,"long_diff":-7954,"short_diff":-5327,"net_diff":-2627},{"variety_en":"CY","long_position":1779,"short_position":2056,"variety_name":"棉纱","quote_date":"2023-04-21","net_position":-277,"long_diff":-203,"short_diff":-335,"net_diff":132},{"variety_en":"EB","long_position":233230,"short_position":225337,"variety_name":"苯乙烯","quote_date":"2023-04-21","net_position":7893,"long_diff":-102,"short_diff":-4079,"net_diff":3977},{"variety_en":"EG","long_position":341562,"short_position":402815,"variety_name":"乙二醇","quote_date":"2023-04-21","net_position":-61253,"long_diff":10954,"short_diff":5936,"net_diff":5018},{"variety_en":"FB","long_position":0,"short_position":0,"variety_name":"纤板","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"FG","long_position":1099934,"short_position":1136726,"variety_name":"玻璃","quote_date":"2023-04-21","net_position":-36792,"long_diff":-72542,"short_diff":-55929,"net_diff":-16613},{"variety_en":"FU","long_position":317920,"short_position":290055,"variety_name":"燃料油","quote_date":"2023-04-21","net_position":27865,"long_diff":-15455,"short_diff":-11890,"net_diff":-3565},{"variety_en":"HC","long_position":702551,"short_position":650685,"variety_name":"热卷","quote_date":"2023-04-21","net_position":51866,"long_diff":21167,"short_diff":12830,"net_diff":8337},{"variety_en":"I","long_position":962840,"short_position":937529,"variety_name":"铁矿","quote_date":"2023-04-21","net_position":25311,"long_diff":-820,"short_diff":10665,"net_diff":-11485},{"variety_en":"IC","long_position":229948,"short_position":236428,"variety_name":"中证500","quote_date":"2023-04-21","net_position":-6480,"long_diff":6425,"short_diff":4773,"net_diff":1652},{"variety_en":"IF","long_position":143528,"short_position":169898,"variety_name":"沪深","quote_date":"2023-04-21","net_position":-26370,"long_diff":-578,"short_diff":-2895,"net_diff":2317},{"variety_en":"IH","long_position":89729,"short_position":113997,"variety_name":"上证","quote_date":"2023-04-21","net_position":-24268,"long_diff":-1048,"short_diff":-2603,"net_diff":1555},{"variety_en":"IM","long_position":127803,"short_position":138050,"variety_name":"中证1000","quote_date":"2023-04-21","net_position":-10247,"long_diff":6093,"short_diff":4888,"net_diff":1205},{"variety_en":"J","long_position":22404,"short_position":22289,"variety_name":"焦炭","quote_date":"2023-04-21","net_position":115,"long_diff":505,"short_diff":559,"net_diff":-54},{"variety_en":"JD","long_position":130675,"short_position":137058,"variety_name":"鸡蛋","quote_date":"2023-04-21","net_position":-6383,"long_diff":284,"short_diff":1089,"net_diff":-805},{"variety_en":"JM","long_position":42182,"short_position":47455,"variety_name":"焦煤","quote_date":"2023-04-21","net_position":-5273,"long_diff":1740,"short_diff":1962,"net_diff":-222},{"variety_en":"JR","long_position":0,"short_position":0,"variety_name":"粳稻","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"L","long_position":527724,"short_position":531455,"variety_name":"塑料","quote_date":"2023-04-21","net_position":-3731,"long_diff":6504,"short_diff":-1326,"net_diff":7830},{"variety_en":"LH","long_position":34618,"short_position":39444,"variety_name":"生猪","quote_date":"2023-04-21","net_position":-4826,"long_diff":408,"short_diff":887,"net_diff":-479},{"variety_en":"LR","long_position":0,"short_position":0,"variety_name":"晚稻","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"LU","long_position":169128,"short_position":178154,"variety_name":"低硫燃油","quote_date":"2023-04-21","net_position":-9026,"long_diff":-7123,"short_diff":-7457,"net_diff":334},{"variety_en":"M","long_position":1827069,"short_position":1999858,"variety_name":"豆粕","quote_date":"2023-04-21","net_position":-172789,"long_diff":-14863,"short_diff":-7303,"net_diff":-7560},{"variety_en":"MA","long_position":1440111,"short_position":1510685,"variety_name":"甲醇","quote_date":"2023-04-21","net_position":-70574,"long_diff":-159634,"short_diff":-160474,"net_diff":840},{"variety_en":"NI","long_position":116950,"short_position":118391,"variety_name":"沪镍","quote_date":"2023-04-21","net_position":-1441,"long_diff":-1028,"short_diff":478,"net_diff":-1506},{"variety_en":"NR","long_position":75259,"short_position":78865,"variety_name":"20号胶","quote_date":"2023-04-21","net_position":-3606,"long_diff":-510,"short_diff":-1105,"net_diff":595},{"variety_en":"OI","long_position":259256,"short_position":310636,"variety_name":"菜油","quote_date":"2023-04-21","net_position":-51380,"long_diff":-1058,"short_diff":2208,"net_diff":-3266},{"variety_en":"P","long_position":626404,"short_position":676975,"variety_name":"棕榈油","quote_date":"2023-04-21","net_position":-50571,"long_diff":-11747,"short_diff":-3392,"net_diff":-8355},{"variety_en":"PB","long_position":60903,"short_position":63528,"variety_name":"沪铅","quote_date":"2023-04-21","net_position":-2625,"long_diff":-1520,"short_diff":-2514,"net_diff":994},{"variety_en":"PF","long_position":588890,"short_position":587681,"variety_name":"短纤","quote_date":"2023-04-21","net_position":1209,"long_diff":-2647,"short_diff":1783,"net_diff":-4430},{"variety_en":"PG","long_position":70252,"short_position":79319,"variety_name":"LPG","quote_date":"2023-04-21","net_position":-9067,"long_diff":-13801,"short_diff":-14853,"net_diff":1052},{"variety_en":"PK","long_position":74604,"short_position":78746,"variety_name":"花生","quote_date":"2023-04-21","net_position":-4142,"long_diff":-5293,"short_diff":-7264,"net_diff":1971},{"variety_en":"PM","long_position":0,"short_position":0,"variety_name":"普麦","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"PP","long_position":451034,"short_position":522561,"variety_name":"聚丙烯","quote_date":"2023-04-21","net_position":-71527,"long_diff":-12170,"short_diff":-11384,"net_diff":-786},{"variety_en":"RB","long_position":1893192,"short_position":1990526,"variety_name":"螺纹","quote_date":"2023-04-21","net_position":-97334,"long_diff":9446,"short_diff":46503,"net_diff":-37057},{"variety_en":"RI","long_position":0,"short_position":0,"variety_name":"早稻","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"RM","long_position":464017,"short_position":495663,"variety_name":"菜粕","quote_date":"2023-04-21","net_position":-31646,"long_diff":176,"short_diff":731,"net_diff":-555},{"variety_en":"RR","long_position":19673,"short_position":19862,"variety_name":"粳米","quote_date":"2023-04-21","net_position":-189,"long_diff":19673,"short_diff":19862,"net_diff":-189},{"variety_en":"RS","long_position":36,"short_position":36,"variety_name":"菜籽","quote_date":"2023-04-21","net_position":0,"long_diff":-4,"short_diff":-4,"net_diff":0},{"variety_en":"RU","long_position":189908,"short_position":265208,"variety_name":"天然橡胶","quote_date":"2023-04-21","net_position":-75300,"long_diff":-2026,"short_diff":-2727,"net_diff":701},{"variety_en":"SA","long_position":839061,"short_position":886219,"variety_name":"纯碱","quote_date":"2023-04-21","net_position":-47158,"long_diff":-25497,"short_diff":-34278,"net_diff":8781},{"variety_en":"SC","long_position":0,"short_position":0,"variety_name":"原油","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"SF","long_position":373134,"short_position":389930,"variety_name":"硅铁","quote_date":"2023-04-21","net_position":-16796,"long_diff":15510,"short_diff":13267,"net_diff":2243},{"variety_en":"SI","long_position":66127,"short_position":78885,"variety_name":"工业硅","quote_date":"2023-04-21","net_position":-12758,"long_diff":-1601,"short_diff":-2534,"net_diff":933},{"variety_en":"SM","long_position":387665,"short_position":378231,"variety_name":"锰硅","quote_date":"2023-04-21","net_position":9434,"long_diff":22366,"short_diff":19092,"net_diff":3274},{"variety_en":"SN","long_position":68010,"short_position":69283,"variety_name":"沪锡","quote_date":"2023-04-21","net_position":-1273,"long_diff":-3237,"short_diff":-3748,"net_diff":511},{"variety_en":"SP","long_position":272625,"short_position":335324,"variety_name":"纸浆","quote_date":"2023-04-21","net_position":-62699,"long_diff":3324,"short_diff":2149,"net_diff":1175},{"variety_en":"SR","long_position":872859,"short_position":832211,"variety_name":"白糖","quote_date":"2023-04-21","net_position":40648,"long_diff":19733,"short_diff":5639,"net_diff":14094},{"variety_en":"SS","long_position":78061,"short_position":86196,"variety_name":"不锈钢","quote_date":"2023-04-21","net_position":-8135,"long_diff":-2336,"short_diff":-3663,"net_diff":1327},{"variety_en":"T","long_position":185248,"short_position":176401,"variety_name":"十债","quote_date":"2023-04-21","net_position":8847,"long_diff":5416,"short_diff":4285,"net_diff":1131},{"variety_en":"TA","long_position":1763480,"short_position":1817037,"variety_name":"PTA","quote_date":"2023-04-21","net_position":-53557,"long_diff":-50098,"short_diff":-58761,"net_diff":8663},{"variety_en":"TF","long_position":90342,"short_position":90910,"variety_name":"五债","quote_date":"2023-04-21","net_position":-568,"long_diff":3558,"short_diff":3260,"net_diff":298},{"variety_en":"TS","long_position":52529,"short_position":57710,"variety_name":"二债","quote_date":"2023-04-21","net_position":-5181,"long_diff":1393,"short_diff":1065,"net_diff":328},{"variety_en":"UR","long_position":261613,"short_position":275270,"variety_name":"尿素","quote_date":"2023-04-21","net_position":-13657,"long_diff":-9825,"short_diff":770,"net_diff":-10595},{"variety_en":"V","long_position":804696,"short_position":853319,"variety_name":"PVC","quote_date":"2023-04-21","net_position":-48623,"long_diff":-18425,"short_diff":-26507,"net_diff":8082},{"variety_en":"WH","long_position":0,"short_position":0,"variety_name":"强麦","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"WR","long_position":0,"short_position":0,"variety_name":"线材","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"Y","long_position":668708,"short_position":747560,"variety_name":"豆油","quote_date":"2023-04-21","net_position":-78852,"long_diff":18816,"short_diff":35328,"net_diff":-16512},{"variety_en":"ZC","long_position":0,"short_position":0,"variety_name":"动力煤","quote_date":"2023-04-21","net_position":0,"long_diff":0,"short_diff":0,"net_diff":0},{"variety_en":"ZN","long_position":119135,"short_position":132653,"variety_name":"沪锌","quote_date":"2023-04-21","net_position":-13518,"long_diff":150,"short_diff":-1237,"net_diff":1387}],"date_list":["2023-04-21"]}}
    data = data['data']['rank_data']
    in_data = []
    for r in data:
        # print(r)
        in_data.append({
            'date': r['quote_date'],
            'code': r['variety_en'],
            'name': r['variety_name'],
            'long': r['long_position'],
            'long_change': r['long_diff'],
            'short': r['short_position'],
            'short_change': r['short_diff'],
        })

    sql = """
        INSERT INTO ruizhi.quote_future_position (date, code, name, `long`, long_change, short, short_change)
        VALUES (%(date)s,%(code)s,%(name)s,%(long)s,%(long_change)s,%(short)s,%(short_change)s);
    """
    with BaseDatabaseConnection() as conn:
        c = conn.cursor.executemany(sql, in_data)
        print('插入完成', c)


def add_short_message():
    url = 'https://210.13.218.130:9000/api/info/stm/?page=1&page_size=50'
    r = requests.get(url, verify=False)
    save_url = 'http://127.0.0.1:8000/v1/infor/shortMessage/save/'
    for row in r.json()['data']:
        body = {
            'msg_time': row['create_time'],
            'msg_type': 'short',
            'content': row['content']
        }
        print(body)
        r = requests.post(save_url, json=body)
        print(r.json())
        time.sleep(1)

def add_fast_message():
    with open('fast.json', 'r', encoding='utf8') as f:
        c = json.load(f)
    c = c['LivesList']
    save_url = 'http://127.0.0.1:8000/v1/infor/shortMessage/save/'
    for row in c:
        body = {
            'msg_time': row['showtime'],
            'msg_type': 'fast',
            'content': row['digest']
        }
        r = requests.post(save_url, json=body)
        print(r.json())
        time.sleep(1)


def add_if_main_price():
    df = pd.read_excel('沪深300.xlsx')
    df.sort_values(by='time', inplace=True)
    data = df.to_dict(orient='records')
    sql = """
        INSERT INTO ruizhi_db1.ruizhi_r0000000002 (datadate,datavalue)
        VALUES (%(time)s,%(close)s);
    """
    with BaseDatabaseConnection() as conn:
        c = conn.cursor.executemany(sql, data)
        print('插入完成', c)


if __name__ == '__main__':
    # add_future_spot_basic()
    # add_future_position()
    # add_short_message()
    # add_fast_message()
    add_if_main_price()
